﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using MySql.Data;
using System.Data;
using System.Data.SqlClient;
namespace SaleWebsite.Models
{
    public class MySqlImplement
    {
        MySqlConnection con;
        MySqlCommand command;
        private static MySqlImplement instance=null;
        public static MySqlImplement GetInstance()
        {
            if (instance==null)
            {
                instance = new MySqlImplement();
            }
            return instance;
        }
        private MySqlImplement()
        {
            con = new MySqlConnection(System.Configuration.ConfigurationManager.AppSettings["connection"]);
            command = new MySqlCommand();
        }
        public void Open()
        {
            if ( con.State==ConnectionState.Closed)
            {
                con.Open();
            }
        }
        private void Close()
        {
            if (con.State==ConnectionState.Open)
            {
                con.Close();
            }
        }
        public ulong ExcuteQuery(IEnumerable<MySqlParameter>list,string storeName)
        {
            
            try
            {
                Open();
                command.Connection = con;
                command.CommandText = storeName;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();
                foreach (var item in list)
                {
                    command.Parameters.Add(item);
                }
                return (ulong)command.ExecuteScalar() ;

            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            finally
            {
                Close();
               
            }
        }
        public DataSet GetQuery(string storeName)
        {
            MySqlDataAdapter adaper=null;
            try
            {
                Open();
                command.Connection = con;
                command.CommandText = storeName;
                command.Parameters.Clear();
                adaper = new MySqlDataAdapter(command);
                DataSet dataset = new DataSet();
                adaper.Fill(dataset);
                return dataset;
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            finally
            {
                Close();
                adaper.Dispose();
            }
        }
        public DataSet GetQuery(string storeName,IEnumerable<MySqlParameter>list)
        {
            MySqlDataAdapter adaper = null;
            try
            {
                Open();
                command.Connection = con;
                command.CommandText = storeName;
                command.Parameters.Clear();
                foreach (var item in list)
                {
                    command.Parameters.Add(item);
                }
                adaper = new MySqlDataAdapter(command);
                DataSet dataset = new DataSet();
                adaper.Fill(dataset);
                return dataset;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                Close();
                adaper.Dispose();
            }
        }
        public int ExcuteCommand(IEnumerable<MySqlParameter>list,string storeName)
        {
            try
            {
                Open();
                command.Connection = con;
                command.CommandText = storeName;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();
                foreach (var item in list)
                {
                    command.Parameters.Add(item);
                }
                return command.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                Close();

            }
        }
    }
}